﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Web;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using System.Text.RegularExpressions;
using NPOI.XSSF.UserModel;

namespace ConsolidationTool.Common
{
    public class JsonToExcel
    {
        /// <summary>
        /// DataTable转换成Excel文档流，并保存到文件
        /// </summary> 
        /// <param name="fileName">保存的路径</param>
        public void Convert(ExcelTable excelTable, HttpContext context, string fileName, Stream stream)
        {
            using (MemoryStream ms = RenderToExcelStream(excelTable, stream))
            {
                SaveToBrowser(ms, context, fileName);
            }
        }

        /// <summary>
        /// DataTable转换成Excel文档流
        /// </summary> 
        public MemoryStream RenderToExcelStream(ExcelTable excelTable, Stream stream)
        {
            IWorkbook workbook = null;
            if (stream == null)
                workbook = new XSSFWorkbook();
            else
                workbook = WorkbookFactory.Create(stream);

            MemoryStream ms = new MemoryStream();

            for (int i = 0; i < excelTable.sheets.Count; i++)
            {
                var row = excelTable.sheets[i].rows;
                string name = excelTable.sheets[i].name == "" ? "sheet" + i : excelTable.sheets[i].name;
                var sheet = workbook.GetSheet(name);
                if (sheet == null)
                    sheet = workbook.CreateSheet(name);

                SetExcelCellValue(workbook, sheet, row, 0, 0); //绑定Execl
            }
            workbook.Write(ms);
            ms.Flush();
            //ms.Position = 0;
            return ms;
        }

        public void SetExcelCellValue(IWorkbook workbook, ISheet sheet, List<TableRow> rows, int StartRowIndex, int StartCellIndex)
        {
            var cellstyle = workbook.CreateCellStyle();
            var headerStyle = workbook.CreateCellStyle();
            var titleStyle = workbook.CreateCellStyle();

            int CellStart = 0;
            int CellEnd = 0;
            for (int rowindex = 0; rowindex < rows.Count; rowindex++)
            {
                var row = rows[rowindex];
                if (row.emptyRow)
                    continue;

                var format = workbook.CreateDataFormat();

                //获取可编辑的行
                var sheetRow = sheet.GetRow(rowindex + StartRowIndex);
                if (sheetRow == null)
                    sheetRow = sheet.CreateRow(rowindex + StartRowIndex);

                bool hasMergedRegion = true;

                #region
                foreach (var column in row.columns)
                {
                    string cellStringTempValue = column.Content.Trim();
                    if (!hasMergedRegion)
                    {
                        CellStart = CellEnd;
                        CellEnd = CellStart + column.CellWidth - 1;
                    }
                    else
                    {
                        CellStart = column.CellIndex + StartCellIndex;
                        CellEnd = column.CellIndex + StartCellIndex + column.CellWidth - 1;
                    }
                    #region 判断合并单元格
                    if (column.CellWidth > 1 || column.CellHeight > 1)
                    {
                        int RowStart = rowindex + StartRowIndex;
                        int RowEnd = rowindex + StartRowIndex + column.CellHeight - 1;

                        //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                        CellRangeAddress CellRangeAddress = new CellRangeAddress(RowStart, RowEnd, CellStart, CellEnd);
                        sheet.AddMergedRegion(CellRangeAddress);
                        hasMergedRegion = false;
                    }
                    #endregion

                    //给单元格添加样式
                    var sheetColumn = sheetRow.CreateCell(CellStart);//创建单元格

                    if (row.headRow)
                    {
                        headerStyle.Alignment = (HorizontalAlignment)row.rowStyle;

                        double result;
                        if (row.headRow && double.TryParse(column.Content, out result))
                        {
                            sheetColumn.SetCellValue(result);
                            cellStringTempValue = sheetColumn.NumericCellValue + "";
                        }
                        else
                            sheetColumn.SetCellValue(column.Content);

                        sheetColumn.CellStyle = headerStyle;
                    }
                    else if (row.titleRow)
                    {
                        titleStyle.Alignment = (HorizontalAlignment)row.rowStyle;
                        titleStyle.DataFormat = format.GetFormat("0.00");

                        double result;
                        if (row.titleRow && double.TryParse(column.Content, out result))
                        {
                            sheetColumn.SetCellValue(result);
                            cellStringTempValue = sheetColumn.NumericCellValue + "";
                        }
                        else
                            sheetColumn.SetCellValue(column.Content);

                        sheetColumn.CellStyle = titleStyle;
                    }
                    else
                    {
                        double result;
                        if (double.TryParse(cellStringTempValue, out result))
                        {
                            var doubleStyle = workbook.CreateCellStyle();
                            sheetColumn.SetCellValue(result);
                            string decmil = "0";
                            if (cellStringTempValue.IndexOf('.') > 0)
                            {
                                int length = cellStringTempValue.Split('.')[1].Length;
                                if (length == 1)
                                    decmil = "0.0";
                                else if (length == 2)
                                    decmil = "0.00";
                                else if (length == 3)
                                    decmil = "0.000";
                                else if (length == 4)
                                    decmil = "0.0000";

                                doubleStyle.DataFormat = format.GetFormat("#,##" + decmil);
                            }
                            else
                                doubleStyle.DataFormat = format.GetFormat("#,##0");

                            cellstyle.Alignment = HorizontalAlignment.RIGHT;
                            sheetColumn.CellStyle = doubleStyle;//给单元格赋样式
                            cellStringTempValue = sheetColumn.NumericCellValue + ",," + decmil;
                        }
                        else
                        {
                            sheetColumn.SetCellValue(column.Content);
                        }
                    }
                    // 对每一列都处理宽度
                    if (cellStringTempValue.Length > 0 && cellStringTempValue.Length < 150)
                    {
                        int toBeSetWidth = (int)(((double)cellStringTempValue.Length + 0.05) * 256);//0.27
                        int currentWidth = sheet.GetColumnWidth(CellStart);
                        if (currentWidth < toBeSetWidth)
                            sheet.SetColumnWidth(CellStart, toBeSetWidth);
                    }
                    if (!hasMergedRegion)
                        CellEnd += 1;
                }

                #endregion
            }
        }

        /// <summary>
        /// 输出文件到浏览器
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">文件名</param>
        private void SaveToBrowser(MemoryStream ms, HttpContext context, string fileName)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.BinaryWrite(ms.ToArray());

            context.Response.Flush();
            context.Response.End();
        }

    }
    public class ExcelTable
    {
        public string fileName { get; set; }
        public List<TableSheet> sheets { get; set; }
    }
    public class TableSheet
    {
        public string name { get; set; }
        public int[] columnWidth { get; set; }

        public List<TableRow> rows { get; set; }
    }

    public class TableRow
    {
        public int index { get; set; }
        public int rowStyle { get; set; }
        public bool headRow { get; set; }
        public bool titleRow { get; set; }
        public bool emptyRow { get; set; }

        public List<TableColumn> columns { get; set; }

    }

    public class TableColumn
    {
        public TableColumn()
        {
            CellWidth = 1;
            CellHeight = 1;
        }
        public int CellIndex { get; set; }
        public string Content { get; set; }
        public int CellWidth { get; set; }
        public int CellHeight { get; set; }
    }
}
